<?php
	////////////////////////////
	//		Fonctions USER    //
	////////////////////////////
		////////////////////////////
		//		getUser...		  //
		////////////////////////////
		function getAllUser($l = NULL){
			$limit = (int)$l;
			$sql ='SELECT *
			FROM user
			ORDER BY user.subscribe DESC ';
			if($limit){
				$sql .= 'LIMIT 0, '.$limit.';';
			}
			return fetch($sql);
		}

		function getUserById($id){
			$sql = 'SELECT id_user, firstname, lastname, rights, mail, birthday FROM user WHERE id_user = '.$id.'';
			return fetch($sql);
		}

		function getUserByMail($mail){
			$sql = 'SELECT id_user, activate, activation_key FROM user
			WHERE mail = "'.$mail.'"';
			return fetch($sql);
		}

		function getUserByLogin($mail, $pwd){
			$sql = 'SELECT * FROM user WHERE mail = "'.$mail.'" AND password = MD5("'.$pwd.'")';
			return fetch($sql);
		}

		function getUserByCategorie($categoryTab){
			$categorie_sql = implode(',',$categoryTab);
			$sql = 'SELECT DISTINCT user.firstname,user.lastname FROM user 
			INNER JOIN skill ON user.id_user = skill.id_user
			INNER JOIN categorie ON skill.id_categorie = categorie.id_categorie
			WHERE categorie.id_categorie IN ('.$categorie_sql.') ';
			return fetch($sql);
		}

		function getUserByKey($key){
			$sql = 'SELECT id_user, firstname, lastname, rights FROM user
			WHERE activation_key = "'.$key.'"';
			return fetch($sql);
		}

		function getAllUsersAvailabilityLimited($datetime, $limit , $offset, $id_user=null){ 	/* faire un offset++ a chaque fois qu'on apuuye sur suivant*/ 
			$A = $offset*$limit;
			$sql = 'SELECT user.id_user, user.firstname, user.lastname, availability.id_availability, DAYNAME(availability.date_begin) as dayweek, DAY(availability.date_begin) as daymonth, DATE_FORMAT(availability.date_begin, "%d/%m") as dayyear, availability.date_begin, availability.duration, availability.daily, availability.weekly, availability.monthly, availability.yearly, availability.price, availability.active FROM user
			INNER JOIN availability ON availability.id_user = user.id_user
			WHERE availability.date_begin <= "'.$datetime.'" 
			AND (DATE_FORMAT(availability.date_begin,"%Y-%m-%d")  = DATE_FORMAT("'.$datetime.'","%Y-%m-%d") 
				OR availability.daily = 1
				OR (DAYNAME(availability.date_begin) = DAYNAME("'.$datetime.'") AND availability.weekly = 1)
				OR (DAYOFMONTH(availability.date_begin) = DAYOFMONTH("'.$datetime.'") AND availability.monthly = 1)
				OR (DAYOFMONTH(availability.date_begin) = DAYOFMONTH("'.$datetime.'") AND availability.yearly = 1))
			AND( NOT EXISTS (SELECT time.date FROM time WHERE time.date = availability.date_begin))
			AND( user.id_user != "'.$id_user.'")
			AND availability.active != 0 
			LIMIT '.$A.', '.$limit.'';
			return fetch($sql);
		}

		function getUserByMultipleArgsLimited($datetime, $id_categories, $reccurence, $keyword, $order, $limit = 10, $offset = 0, $id_user = null){
			$categorie_sql = implode(',',$id_categories);
			$A = $offset*$limit;
			$sql = 'SELECT DISTINCT user.id_user, user.firstname, user.lastname, availability.id_availability, DAYNAME(availability.date_begin) as dayweek, DAY(availability.date_begin) as daymonth, DATE_FORMAT(availability.date_begin, "%d/%m") as dayyear, availability.date_begin, availability.duration, availability.daily, availability.weekly, availability.monthly, availability.yearly, availability.price, availability.active FROM user
			INNER JOIN skill ON skill.id_user = user.id_user
			INNER JOIN categorie ON skill.id_categorie = categorie.id_categorie
			INNER JOIN availability ON availability.id_user = user.id_user
			WHERE( NOT EXISTS (SELECT time.date FROM time WHERE time.date = availability.date_begin))
			AND( user.id_user != "'.$id_user.'") 
			AND availability.active != 0 ';
			if($reccurence == 'all'){
	        	$sql .= ' AND DATE_FORMAT(availability.date_begin,"%Y-%m-%d") <= DATE_FORMAT("2013-05-31 18:12:32","%Y-%m-%d") ';
	        }
	        else{
	        	if ($reccurence == 'none'){
	            	$sql .= 'AND (';
	            	$sql .= ' DATE_FORMAT(availability.date_begin,"%Y-%m-%d") = DATE_FORMAT("2013-05-31 18:12:32","%Y-%m-%d") ';
	            	$sql .= ') ';
	            }
	            if ($reccurence == 'daily'){
	            	$sql .= ' AND (';
	            	$sql .= ' availability.date_begin <= "'.$datetime.'"';
	            	$sql .= 'AND availability.daily = 1 ';
	            	$sql .= ') ';
	            }
	            if ($reccurence == 'weekly'){
	            	$sql .= ' AND (';
	            	$sql .= ' availability.date_begin <= "'.$datetime.'"';
	            	$sql .= 'AND (DAYNAME(availability.date_begin) = DAYNAME("'.$datetime.'") AND availability.weekly = 1) ';
	            	$sql .= ') ';
	            }
	            if ($reccurence == 'monthly'){
	            	$sql .= ' AND (';
	            	$sql .= ' availability.date_begin <= "'.$datetime.'"';
	            	$sql .= 'AND (DAYOFMONTH(availability.date_begin) = DAYOFMONTH("'.$datetime.'") AND availability.monthly = 1) ';
	            	$sql .= ') ';
	            }
	            if ($reccurence == 'yearly'){
	            	$sql .= ' AND (';
	            	$sql .= ' availability.date_begin <= "'.$datetime.'"';
	            	$sql .= 'AND (DAYOFMONTH(availability.date_begin) = DAYOFMONTH("'.$datetime.'") AND availability.yearly = 1) ';
	            	$sql .= ') ';
	            }
	        }
			if($categorie_sql || $keyword){
	             if ($categorie_sql) {
	                $sql .= ' AND (';
	                $sql .= 'categorie.id_categorie IN ( '.mysql_real_escape_string($categorie_sql).')';
	                $sql .= ') ';
	            }
	            if ($keyword) {
	                $sql .= ' AND (';
	                $sql .= 'availability.date_begin LIKE \'%'.mysql_real_escape_string($keyword).'%\' 
	                OR user.firstname LIKE \'%'.mysql_real_escape_string($keyword).'%\'
	                OR user.lastname LIKE \'%'.mysql_real_escape_string($keyword).'%\'
	                OR categorie.name LIKE \'%'.mysql_real_escape_string($keyword).'%\'';
	                $sql .= ') ';
	            }
	        }
	        if ($order){
	        	switch ($order) {
	        		case 'price_c':
	                    $order = 'availability.price ASC';
	                    break;
	                case 'price_d':
	                    $order = 'availability.price DESC';
	                    break;
	                case 'time_c':
	                    $order = 'availability.duration ASC';
	                    break;
	                case 'time_d':
	                	$order = 'availability.duration DESC';
	                	break;
	        		default:
	        			$order = 'price ASC';
	        			break;
	        	}
	            $sql .= ' ORDER BY '.$order.' ';
	        }
	        $sql .= ' LIMIT '.$A.', '.$limit.';';
	        return fetch($sql);
		}

		////////////////////////////
		//		InsertUser...	  //
		////////////////////////////
		function insertUser($mail, $password, $firstname = null, $lastname = null, $birthday = null, $key = null){		/* Verifier si $mail est bien un mail avec @ et tt et tt */
			$sql = 'INSERT INTO user (firstname, lastname, birthday, mail, password, activation_key) VALUES ("'.$firstname.'", "'.$lastname.'", "'.$birthday.'", "'.$mail.'", MD5("'.$password.'"), "'.$key.'")';
			return mysql_query($sql);
		}

		////////////////////////////
		//		updateUser...	  //
		////////////////////////////
		function updateUser($password, $newP, $id_user){
			$sql = 'UPDATE user SET password = MD5("'.$newP.'")
			WHERE id_user = '.$id_user.' AND password = MD5("'.$password.'")';
			return mysql_query($sql);
		}

		function updateUserRights($id, $rights){
			$sql = 'UPDATE user SET rights = "'.$rights.'"
			WHERE id_user = "'.$id.'"';
			return mysql_query($sql);
		}

		function updateUserBan($id, $ban){
			$sql = 'UPDATE user SET ban = "'.$ban.'"
			WHERE id_user = "'.$id.'"';
			return mysql_query($sql);
		}

		function updateUserActive($id, $active){
			$sql = 'UPDATE user SET activate = '.$active.'
			WHERE id_user = '.$id.'';
			return mysql_query($sql);
		}

		////////////////////////////
		//		othersOnTable...  //
		////////////////////////////
		function getRights(){
		    $sql = "SELECT COLUMN_TYPE
					FROM INFORMATION_SCHEMA.COLUMNS
					WHERE TABLE_SCHEMA = 'timeshop_db'
					AND TABLE_NAME = 'user'
					AND COLUMN_NAME = 'rights'";
			$types = fetch($sql);
		    preg_match('/^enum\((.*)\)$/', $types[0]["COLUMN_TYPE"], $matches);
		    foreach( explode(',', $matches[1]) as $value )
		    {
		         $enum[] = trim( $value, "'" );
		    }
		    return $enum;
		}

		function checkUserAvailability($id_user,$firstname,$lastname,$duration,$price,$id_availability){
			$sql = 'SELECT user.id_user, user.firstname, user.lastname, availability.id_availability, availability.date_begin, availability.duration, availability.daily, availability.weekly, availability.monthly, availability.yearly, availability.price FROM user
			INNER JOIN availability ON availability.id_user = user.id_user
			WHERE user.id_user = '.$id_user.'
			AND (user.firstname = "'.$firstname.'")
			AND (user.lastname = "'.$lastname.'")
			AND (availability.duration = "'.$duration.'")
			AND (availability.price = '.$price.')
			AND (availability.id_availability = '.$id_availability.')';
			return fetch($sql);
		}

		function countUsersByMonth($date){
			$sql = 'SELECT count(user.id_user) FROM user
				WHERE DATE_FORMAT(user.subscribe, "%m-%Y") = DATE_FORMAT("'.$date.'", "%m-%Y")';
			return fetch($sql);
		}

		function countUsersByCategorie($id_categorie){
			$sql = 'SELECT count(categorie.id_categorie) as count, categorie.name FROM `categorie`
				INNER JOIN skill ON skill.id_categorie = categorie.id_categorie
				WHERE categorie.id_categorie = "'.$id_categorie.'"';
			return fetch($sql);
		}